package org.zty.service;

import java.util.ArrayList;
import java.util.List;

import org.zty.domain.Attr;
import org.zty.domain.Pojo;

public class ToSqlXMLStr {
	public String toSqlXML(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>");
		sb.append("\r\n");
		sb.append("<!DOCTYPE sqlMap PUBLIC \"-//ibatis.apache.org//DTD SQL Map 2.0//EN\" \"http://ibatis.apache.org/dtd/sql-map-2.dtd\">");
		sb.append("\r\n");
		sb.append("<sqlMap namespace=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("<typeAlias alias=\""+pojo.getName()+"\" type=\"*包全路径*"+pojo.getName()+"\" />");
		sb.append("\r\n\t");
		sb.append(insertTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n\r\n\t");
		sb.append(updateTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n\r\n\t");
		sb.append(deleteTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n\r\n\t");
		sb.append(selectTotalCountTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n\r\n\t");
		sb.append(selectTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n\r\n\t");
		sb.append(selectPaginationTable(pojo).replace("\r\n", "\r\n\t"));
		sb.append("\r\n");
		sb.append("</sqlMap>");
		
		return sb.toString();
	}
	
	public String createTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("create table ");
		sb.append(pojo.getTableName());
		sb.append("(\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append("\t"+attr.getTableName());
			if(1==attr.getType()){
				sb.append(" nvarchar(*长度*)");
			}else if(2==attr.getType()){
				sb.append(" int");
			}else if(3==attr.getType()){
				sb.append(" datetime");
			}else{
				sb.append(" nvarchar(*注意格式*)");
			}
			if(i!=attrList.size()-1){
				sb.append(",");
			}
			sb.append("\r\n");
		}
		sb.append(")\r\n");
		return sb.toString();
	}
	
	public String insertTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<insert id=\"insert"+pojo.getName()+"\" parameterClass=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("\tinsert into ");
		sb.append(pojo.getTableName());
		sb.append("(\r\n\t");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append(attr.getTableName());
			if(i!=attrList.size()-1){
				sb.append(",");
			}
		}
		sb.append(") values (");
		sb.append("\r\n\t");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append("#"+attr.getName()+"#");
			if(i!=attrList.size()-1){
				sb.append(",");
			}
		}
		sb.append(")\r\n");
		sb.append("</insert>\r\n");
		return sb.toString();
	}
	
	public String deleteTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<delete id=\"delete"+pojo.getName()+"\" parameterClass=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("\tdelete from ");
		sb.append(pojo.getTableName());
		sb.append("\r\n\t");
		sb.append("<dynamic prepend=\"where\">\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			if(attr.getType()==1){
				//字符型
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}else if(attr.getType()==2){
				//int
				sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEqual>\r\n");
			}else {
				//其他
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}
		}
		sb.append("\t</dynamic>");
		sb.append("\r\n");
		sb.append("</delete>\r\n");
		return sb.toString();
	}
	
	public String updateTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<update id=\"update"+pojo.getName()+"\" parameterClass=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("\tupdate ");
		sb.append(pojo.getTableName()+" set ");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append("\r\n\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
			if(i!=attrList.size()-1){
				sb.append(",");
			}
		}
		
		sb.append("\r\n\t");
		sb.append("<dynamic prepend=\"where\">\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			if(attr.getType()==1){
				//字符型
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}else if(attr.getType()==2){
				//int
				sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEqual>\r\n");
			}else {
				//其他
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}
		}
		sb.append("\t</dynamic>");
		sb.append("\r\n");
		sb.append("</update>\r\n");
		return sb.toString();
	}
	
	public String selectTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<select id=\"find"+pojo.getName()+"\" parameterClass=\""+pojo.getName()+"\" resultClass=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("\tselect ");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append("\r\n\t\t"+attr.getTableName());
			if(i!=attrList.size()-1){
				sb.append(",");
			}
		}
		sb.append("\r\n\tfrom "+pojo.getTableName());
		sb.append("\r\n\t");
		sb.append("<dynamic prepend=\"where\">\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			if(attr.getType()==1){
				//字符型
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}else if(attr.getType()==2){
				//int
				sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEqual>\r\n");
			}else {
				//其他
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}
		}
		sb.append("\t</dynamic>");
		sb.append("\r\n\torder by *此行可删*");
		sb.append("\r\n");
		sb.append("</select>\r\n");
		return sb.toString();
	}
	
	public String selectTotalCountTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<select id=\"find"+pojo.getName()+"Total\" parameterClass=\""+pojo.getName()+"\" resultClass=\"java.lang.Integer\">");
		sb.append("\r\n");
		sb.append("\tselect ");
		sb.append("\r\n\t\tcount(*) as totalSize");
		sb.append("\r\n\tfrom "+pojo.getTableName());
		sb.append("\r\n\t");
		sb.append("<dynamic prepend=\"where\">\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			if(attr.getType()==1){
				//字符型
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}else if(attr.getType()==2){
				//int
				sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEqual>\r\n");
			}else {
				//其他
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}
		}
		sb.append("\t</dynamic>");
		sb.append("\r\n");
		sb.append("</select>\r\n");
		return sb.toString();
	}
	
	public String selectPaginationTable(Pojo pojo){
		StringBuffer sb = new StringBuffer();
		List<Attr> attrList = pojo.getAttrList();
		sb.append("<select id=\"findPaginationList"+pojo.getName()+"\" parameterClass=\""+pojo.getName()+"\" resultClass=\""+pojo.getName()+"\">");
		sb.append("\r\n");
		sb.append("\tselect top $pageSize$ ");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			sb.append("\r\n\t\t"+attr.getTableName());
			if(i!=attrList.size()-1){
				sb.append(",");
			}
		}
		sb.append("\r\n\tfrom "+pojo.getTableName());
		sb.append("\r\n\t");
		sb.append("where *分页ID* not in ");
		sb.append("\r\n\t(");
		sb.append("select top $skipSize$ *分页ID* from "+pojo.getTableName());
		sb.append("\r\n\t");
		sb.append("<dynamic prepend=\"where\">\r\n");
		for(int i =0;i<attrList.size();i++){
			Attr attr = attrList.get(i);
			if(attr.getType()==1){
				//字符型
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}else if(attr.getType()==2){
				//int
				sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEqual>\r\n");
			}else {
				//其他
				sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
				sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
				sb.append("\r\n");
				sb.append("\t\t</isNotEmpty>\r\n");
			}
		}
		sb.append("\t</dynamic>");
		sb.append("\r\n\torder by *此行可删*");
		sb.append("\r\n\t)\r\n\t");

			sb.append("<dynamic>\r\n");
			for(int i =0;i<attrList.size();i++){
				Attr attr = attrList.get(i);
				if(attr.getType()==1){
					//字符型
					sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
					sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
					sb.append("\r\n");
					sb.append("\t\t</isNotEmpty>\r\n");
				}else if(attr.getType()==2){
					//int
					sb.append("\t\t<isNotEqual prepend=\"and\" property=\""+attr.getName()+"\" compareValue =\"0\">");
					sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
					sb.append("\r\n");
					sb.append("\t\t</isNotEqual>\r\n");
				}else {
					//其他
					sb.append("\t\t<isNotEmpty prepend=\"and\" property=\""+attr.getName()+"\" >");
					sb.append("\r\n\t\t\t"+attr.getTableName()+" = #"+attr.getName()+"#");
					sb.append("\r\n");
					sb.append("\t\t</isNotEmpty>\r\n");
				}
			}
			sb.append("\t</dynamic>");
			sb.append("\r\n\torder by *此行可删*");
		
		sb.append("\r\n");
		sb.append("</select>\r\n");
		return sb.toString();
	}
	
	public static void main(String[] args) {
		ToSqlXMLStr ts = new ToSqlXMLStr();
		Pojo p = new Pojo();
		p.setName("Doc");
		Attr attr1=  new Attr();
		attr1.setName("id");
		attr1.setType(2);
		Attr attr2=  new Attr();
		attr2.setName("doctitle");
		attr2.setType(1);
		Attr attr3=  new Attr();
		attr3.setName("subtitle");
		attr3.setType(1);
		Attr attr4=  new Attr();
		attr4.setName("htmlcontent");
		attr4.setType(1);
		Attr attr5=  new Attr();
		attr5.setName("crtime");
		attr5.setType(3);
		Attr attr6=  new Attr();
		attr6.setName("doctime");
		attr6.setType(3);
		List l = new ArrayList<Attr>();
		l.add(attr1);
		l.add(attr2);
		l.add(attr3);
		l.add(attr4);
		l.add(attr5);
		l.add(attr6);
		p.setAttrList(l);
		System.out.println(ts.toSqlXML(p));
		
	}
}
